package com.smartwebx.fr.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Repository;

import com.google.common.base.Objects;
import com.google.common.collect.Lists;
import com.google.common.primitives.Ints;
import com.smartwebx.fr.bean.ColumnInfo;
import com.smartwebx.fr.bean.ProcResultUtil;
import com.smartwebx.fr.bean.SimpleUseArgConf;
import com.smartwebx.fr.util.StationFinalFields;

import oracle.jdbc.driver.OracleTypes;

/**
 * 存储过程查询Dao，用于返回原始的存储过程结果
 * 
 * @author XX
 * 
 */
@Repository
public class StoredProcDao extends JdbcTemplate {
	@Resource
	private JdbcTemplate app;
	@Resource(name = "openAppDataSource")
	private DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void getProResult(final ProcResultUtil procUtil, final Map<String, SimpleUseArgConf> paramNameUseArg) {
		final String callSql = procUtil.getCallSql();
		final Integer returnType = procUtil.getReturnType();
		final boolean hasHeader = procUtil.getHasHeader();
		
		
		app.execute(new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				CallableStatement call = con.prepareCall(callSql);
				switch (returnType) {
				case 0:// table
					call.registerOutParameter(1, OracleTypes.CURSOR);
					break;
				default:
					call.registerOutParameter(1, OracleTypes.VARCHAR);
					break;
				}

				Map<String, String> nameDbType = procUtil.getNameDbType();

				int i = 2;
				for (String name : nameDbType.keySet()) {
					String dbType = nameDbType.get(name);
					SimpleUseArgConf useArg = paramNameUseArg.get(name);
					String showValue = useArg.getVal();

					if (StringUtils.equals("number", dbType)) {
						call.setInt(i, Ints.tryParse(showValue));
					} else {
						call.setString(i, showValue);
					}
					i++;
				}				
				return call;
			}
		}, new CallableStatementCallback<Void>() {
			@Override
			public Void doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();

				switch (returnType) {
				case 0:
					ResultSet rs = (ResultSet) cs.getObject(1);
					getTable(rs, hasHeader, procUtil);
					break;
				default:
					List<List<String>> result = Lists.newArrayList();
					String val = cs.getString(1);
					List<String> line = Lists.newArrayList();
					line.add(val);
					result.add(line);
					procUtil.setResult(result);
					break;
				}
				return null;
			}
		});
	}

	public ProcResultUtil getResultBySql(Entry<String, List<String>> sqlAndParams, final boolean hasHeader) {
		final String callSql = sqlAndParams.getKey();
		final List<String> params = sqlAndParams.getValue();

		final ProcResultUtil proc = new ProcResultUtil();
		app.execute(callSql, new PreparedStatementCallback<Void>() {
			@Override
			public Void doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
				if (!params.isEmpty()) {
					for (int i = 0; i < params.size(); i++) {
						String param = params.get(i);
						if (param.equalsIgnoreCase("null")) {
							pstmt.setNull(i + 1, java.sql.Types.VARCHAR);
						} else {
							pstmt.setString(i + 1, param);
						}
					}
				}
				ResultSet rs = pstmt.executeQuery();
				getTable(rs, hasHeader, proc);

				return null;
			}
		});

		return proc;
	}

	public ProcResultUtil getResultByProc(final String callSql, final String backType, final List<String> params,
			final boolean hasHeader) {
		final ProcResultUtil procBean = new ProcResultUtil();

		app.execute(new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				CallableStatement call = con.prepareCall(callSql);
				if (Objects.equal(backType, "table")) {// 返回的是二维表，使用游标
					call.registerOutParameter(1, OracleTypes.CURSOR);
				} else if (Objects.equal(backType, "signValue")) {// 返回的是单值，不使用游标
					call.registerOutParameter(1, OracleTypes.VARCHAR);
				}
				if (!params.isEmpty()) {
					for (int i = 0; i < params.size(); i++) {
						String param = params.get(i);

						if (param.equalsIgnoreCase("null")) {
							call.setNull(i + 2, java.sql.Types.VARCHAR);
						} else {
							call.setString(i + 2, param);
						}
					}
				}
				return call;
			}
		}, new CallableStatementCallback<Void>() {
			@Override
			public Void doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();

				if (Objects.equal(backType, "table")) {
					try {
						ResultSet rs = (ResultSet) cs.getObject(1);
						getTable(rs, hasHeader, procBean);
					} catch (SQLException sqlException) {
						List<List<String>> result = Lists.newArrayList();
						List<String> line = Lists.newArrayList();
						line.add("没有数据");
						result.add(line);
						procBean.setResult(result);
					}
				} else if (Objects.equal(backType, "signValue")) {
					List<List<String>> result = Lists.newArrayList();
					String val = checkNumberConver(cs.getString(1));
					List<String> line = Lists.newArrayList();
					line.add(val);
					result.add(line);
					procBean.setResult(result);
				}
				return null;
			}
		});

		return procBean;
	}

	private String checkNumberConver(String val) {
		if (NumberUtils.isNumber(val)) {// 判断是否为数字
			try {
				if (NumberUtils.isDigits(val)) {
					return Ints.tryParse(val) + "";
				} else {
					return Double.parseDouble(val) + "";
				}
			} catch (Exception e) {
				return val;
			}
		} else {
			return val;
		}
	}

	private void getTable(ResultSet rs, boolean hasHeader, ProcResultUtil procUtil) throws SQLException {
		List<List<String>> result = Lists.newArrayList();

		List<ColumnInfo> columnInfos = Lists.newArrayList();

		ResultSetMetaData resultMetaData = rs.getMetaData();
		int cols = resultMetaData.getColumnCount();
		List<String> line = Lists.newArrayList();
		Integer[] columnTypes = new Integer[cols];
		for (int i = 1; i <= cols; i++) {
			columnTypes[i - 1] = resultMetaData.getColumnType(i);
			line.add(resultMetaData.getColumnName(i));

			ColumnInfo info = new ColumnInfo();
			info.setName(resultMetaData.getColumnName(i));
			columnInfos.add(info);
		}
		if (hasHeader)
			result.add(line);

		while (rs.next()) {
			line = Lists.newArrayList();
			for (int i = 1; i <= cols; i++) {
				String valStr = null;
				int columnType = columnTypes[i - 1];

				if (columnType == Types.NUMERIC) {
					// 用 string 类型取值，如果用 double 取值 null 会转换成 0.0
					valStr = checkNumberConver(rs.getString(i));
				} else if (columnType == Types.DATE) {
					Timestamp timestamp = rs.getTimestamp(i);
					if (timestamp == null) {
						valStr = StationFinalFields.No_Data_Point;
					} else {
						valStr = DateFormatUtils.format(timestamp, "yyyy-MM-dd HH:mm");
					}
				} else {
					valStr = rs.getString(i);
				}

				if (StringUtils.isBlank(valStr)) {
					line.add(StationFinalFields.No_Data_Point);
				} else {
					line.add(StringUtils.trim(valStr));
				}
			}
			result.add(line);
		}
		// 没有数据时放入空序列
		if (result.isEmpty()) {
			result.add(Lists.newArrayList(StationFinalFields.No_Data_Point));
		}
		procUtil.setResult(result);
		procUtil.setColumnInfos(columnInfos);
	}
	
	@Deprecated
	public ProcResultUtil getResultByStoredProcOther(String callSql, final List<String> paramList) {

		logger.debug(callSql);

		List<List<String>> tableData = new ArrayList<List<String>>();

		tableData = app.execute(callSql, new PreparedStatementCallback<List<List<String>>>() {

			public List<List<String>> doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {

				if (paramList != null) {
					for (int i = 0; i < paramList.size(); i++) {
						String param = paramList.get(i);

						if (param.equalsIgnoreCase("null")) {
							ps.setNull(i + 1, java.sql.Types.VARCHAR);
						} else {
							ps.setString(i + 1, paramList.get(i));
						}
					}
				}

				ResultSet rs = ps.executeQuery();

				ResultSetMetaData rsmd = rs.getMetaData();
				// 获得总列数
				int columnCount = rsmd.getColumnCount();

				List<List<String>> moreLineList = new ArrayList<List<String>>();

				boolean firstIn = true;
				while (rs.next()) {
					List<String> lineList = new ArrayList<String>();

					if (firstIn) {// 加入列名
						firstIn = false;

						for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
							// 获得列名
							lineList.add(rsmd.getColumnName(columnIndex).trim());

						}

						moreLineList.add(lineList);
						lineList = new ArrayList<String>();
					}

					for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {

						String pointData = rs.getString(columnIndex);
						if (pointData != null) {
							lineList.add(pointData.trim());
						} else {
							// 如果结果集为空，则使用指定符号代替
							lineList.add(StationFinalFields.No_Data_Point);
						}

					}
					moreLineList.add(lineList);
				}

				return moreLineList;
			}
		});

		ProcResultUtil procBean = new ProcResultUtil();
		procBean.setResult(tableData);
		return procBean;
	}
}
